package com.example.JavaWebApi.core.excelhelper;

import com.example.JavaWebApi.core.excelhelper.annotation.HeaderMapper;
import com.example.JavaWebApi.core.excelhelper.annotation.OperationType;
import lombok.Data;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.internal.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @program: cms_zjgs_api
 * @description: poi导入导出工具类
 * @author: yang ze
 * @create: 2021-12-13 14:00
 */
@Data
@Component
public class PoiHelper {
    @Value("${spring.web.uploadpath}")
    private String targetFilePath;
    /**
     * Description: 把excel里的内容转化为实体list
     * @param inputStream 文件输入流
	 * @param SheetIndex 需要获取的sheet页索引
	 * @param clazz 需要被转化的模型类型
     * @return java.util.List<T>
     * @author 杨泽
     * @date 2022/4/15 13:24
     */
    @SneakyThrows
    public <T> List<T> excelToObject(InputStream inputStream, int SheetIndex, Class<T> clazz){
        Workbook wb= WorkbookFactory.create(inputStream);
        Sheet sheet=wb.getSheetAt(SheetIndex);
        return sheetToObject(sheet,clazz);
    }

    /**
     * 导入excel到实体list
     * @param FilePath 文件地址
     * @param SheetIndex sheet页索引
     * @param clazz 被转换成的实体类型T
     * @param <T> 泛型T
     * @return 实体list
     */
    @SneakyThrows
    public <T> List<T> excelToObject(String FilePath, int SheetIndex, Class<T> clazz){
        Workbook wb=WorkbookFactory.create(new File(FilePath));
        Sheet sheet=wb.getSheetAt(SheetIndex);
        return sheetToObject(sheet,clazz);
    }

    /**
     * 导入excel到实体list
     * @param FilePath 文件地址
     * @param SheetName sheet页名称
     * @param clazz 被转换成的实体类型T
     * @param <T> 泛型T
     * @return 实体list
     */
    @SneakyThrows
    public <T> List<T> excelToObject(String FilePath, String SheetName, Class<T> clazz){
        Workbook wb=WorkbookFactory.create(new File(FilePath));
        Sheet sheet=wb.getSheet(SheetName);
        return sheetToObject(sheet,clazz);
    }
    /**
     * Description: 获取指定路径中excel的标题头和位置信息
     * @param FilePath 文件路径
	 * @param sheetIndex sheet索引
	 * @param headerIndex 标题头索引
     * @return java.util.HashMap<java.lang.String,java.lang.Integer>
     * @author 杨泽
     * @date 2022/4/19 15:15
     */
    public HashMap<String,Integer> getHeaderMap(String FilePath, int sheetIndex,int headerIndex){
        HashMap<String,Integer> hashMap=new HashMap<>();
        Workbook wb=getWorkBook(FilePath);
        Sheet sheet=wb.getSheetAt(sheetIndex);
        Row header=sheet.getRow(headerIndex);
        int columnNum=header.getLastCellNum();
        for(int i=0;i<=columnNum-1;i++){
            Cell cell=header.getCell(i);
            hashMap.put(cell.getStringCellValue(),i);
        }
        return hashMap;
    }
    /**
     * Description: 对excel文件进行操作
     * @param FilePath 文件地址
	 * @param sheetIndex sheet页索引
     * @param biConsumer 需要执行的委托
     * @return void
     * @author 杨泽
     * @date 2022/4/19 15:02
     */
    @SneakyThrows
    public void doSomethingToExcelFile(String FilePath, int sheetIndex,BiConsumer<Workbook,Sheet> biConsumer){
        Workbook wb=getWorkBook(FilePath);
        Sheet sheet=wb.getSheetAt(sheetIndex);
        biConsumer.accept(wb,sheet);
        FileOutputStream fileOutputStream = null;
        fileOutputStream=new FileOutputStream(FilePath);
        wb.write(fileOutputStream);
        fileOutputStream.close();
    }
    /**
     *
     * @param SheetName sheet页名称
     * @param list 被导出的实体list
     * @param clazz 被导出的实体类型
     * @param <T> 实体类型
     * @return 文件地址
     */
    @SneakyThrows
    public  <T> String exportExcel(String SheetName, List<T> list,Class<T> clazz){
        XSSFWorkbook workbook=new XSSFWorkbook();
        Sheet sheet=null;
        sheet= workbook.createSheet(SheetName);
        sheet=objectToSheet(sheet,list,clazz);

        File folder=new File(targetFilePath);
        //上传文件夹不存在就创建一个
        if (!folder.exists()){
            folder.mkdir();
        }
        String fileName = UUID.randomUUID().toString().replace("-", "")+".xlsx";
        File targetFile = new File(targetFilePath + File.separator + fileName);

        FileOutputStream fileOutputStream = null;
        fileOutputStream = new FileOutputStream(targetFile);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        return fileName;
    }

    /**
     * 泛型list转为sheet页
     * @param sheet sheet页
     * @param list 数据list
     * @param clazz 数据泛型的类型T
     * @param <T> 类型T
     * @return sheet页
     */
    @SneakyThrows
    private <T> Sheet objectToSheet(Sheet sheet, List<T> list, Class<T> clazz){
        LinkedHashMap<String,Field> headerAndProp= getHeaderAndProp(clazz,OperationType.Export);
        int columnIndex = 0;
        int rowIndex = 0;
        //表头赋值
        Row headerRow = sheet.createRow(columnIndex);
        for(String key:headerAndProp.keySet()){
            Cell cell=headerRow.createCell(columnIndex);
            cell.setCellValue(key);
            columnIndex++;
        }
        //索引重置
        columnIndex=0;
        rowIndex++;
        try{
            //数据赋值
            for(T item:list){
                if(item==null) continue;
                Row row=sheet.createRow(rowIndex);
                for(String key:headerAndProp.keySet()){
                    Cell cell=row.createCell(columnIndex);
                    Field propertyField=headerAndProp.get(key);
                    columnIndex++;
                    Object value=propertyField.get(item);
                    if(value==null) continue;
                    if (value instanceof java.util.Date) {
                        java.util.Date d = (java.util.Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String format = sdf.format(d);
                        cell.setCellValue(String.valueOf(format));

                    }
                    else {
                        cell.setCellValue(String.valueOf(value));
                    }
                }
                rowIndex++;
                columnIndex=0;
            }
        }
        catch (Exception e){
            throw new Error("第"+(rowIndex+1)+"条数据的第"+(columnIndex+1)+"列数据转化失败！原始报错信息:"+e.getMessage());
        }

        return sheet;
    }
    @SneakyThrows
    private Workbook getWorkBook(String FilePath){
        Workbook wb;
        String ext=FilePath.substring(FilePath.lastIndexOf("."));
        FileInputStream fs=new FileInputStream(FilePath);
        if (ext.equalsIgnoreCase(".xls")){
            wb=new HSSFWorkbook(fs);
        }
        else if(ext.equalsIgnoreCase(".xlsx")){
            wb=new XSSFWorkbook(fs);
        }
        else {
            throw new Error("文件后缀名未识别！");
        }
        return wb;
    }

    /**
     * sheet转成对象
     * @param sheet poi sheet页
     * @param clazz 泛型的类型
     * @param <T> 泛型T
     * @return 泛型list
     */
    @SneakyThrows
    private <T> List<T> sheetToObject(Sheet sheet,Class<T> clazz){
        //获取有多少行
        int rowNum=sheet.getPhysicalNumberOfRows();
        LinkedHashMap<String,Field> headerAndProp= getHeaderAndProp(clazz,OperationType.Import);
        Row headerRow=sheet.getRow(0);
        //获取有多少列
        int columnNum=headerRow.getLastCellNum();
        List<T> result=new ArrayList<>();
        int rowIndex=0;
        int columnIndex=0;
        try{
            for (int i=1;i<=rowNum-1;i++){

                rowIndex=i;
                // 新建实例
                T item=clazz.newInstance();
                // 获取第一个row
                Row row=sheet.getRow(i);
                for(int j=0;j<=columnNum-1;j++){
                    columnIndex=j;
                    //先获取header名
                    Cell headerCell=headerRow.getCell(j);
                    //表头没数据直接跳过
                    if(headerCell==null){
                        continue;
                    }
                    String headerName=headerCell.toString();
                    Cell data=row.getCell(j);
                    //没数据直接跳过
                    if(data==null){
                        continue;
                    }
                    data.setCellType(CellType.STRING);
                    if (data.getStringCellValue().equals("")){
                        continue;
                    }
                    //表头无匹配项直接跳过
                    if (!headerAndProp.containsKey(headerName)){
                        continue;
                    }
                    else {
                        //获取属性
                        Field propertyField=headerAndProp.get(headerName);
                        propertyField.set(item,convertType(propertyField.getType(),data.getStringCellValue()));
                    }
                }
                result.add(item);
            }

        }
        catch (Exception e){
            throw new Error("第"+(rowIndex)+"条数据的第"+(columnIndex)+"列数据转化失败！原始报错信息:"+e.getMessage());
        }

        return result;
    }

    /**
     * 获取给定类型的注解名和属性名
     * @param clazz 类型名
     * @return
     */
    public LinkedHashMap<String,Field> getHeaderAndProp(Class clazz, OperationType operationType){
        LinkedHashMap<String,Field> headerAndProp=new LinkedHashMap<>();
        List<Field> fields= Arrays.stream(clazz.getDeclaredFields()).collect(Collectors.toList());
        Class superClass=clazz.getSuperclass();
        while (!superClass.equals(Object.class)){
            List<Field> superField= Arrays.stream(superClass.getDeclaredFields()).collect(Collectors.toList());
            fields.addAll(superField);
            superClass=superClass.getSuperclass();
        }
        //排序
        fields=fields.stream().sorted(Comparator.comparing(t->{
            if(t.isAnnotationPresent(HeaderMapper.class)){
                HeaderMapper headerMapper=t.getAnnotation(HeaderMapper.class);
                return headerMapper.position();
            }
            return 999;
        })).collect(Collectors.toList());
        //先获取全部的属性映射信息
        for(Field f : fields){
            //如果进行了标记了注解 再进行处理
            if(f.isAnnotationPresent(HeaderMapper.class)){
                HeaderMapper headerMapper=f.getAnnotation(HeaderMapper.class);
                if(!StringUtil.isBlank(headerMapper.mapperName())){
                    if (headerMapper.operationType().equals(OperationType.Import_Export)||headerMapper.operationType().equals(operationType)){
                        f.setAccessible(true);
                        headerAndProp.put(headerMapper.mapperName(),f);
                    }
                }
            }
        }
        return headerAndProp;
    }

    /**
     * 类型转化
     * @param clazz 转化的类型
     * @param value 传入的值
     * @return
     */
    private Object convertType(Class clazz, String value) {
        if (Integer.class == clazz || int.class == clazz) {
            return Integer.valueOf(value.equals("")?"0":value);
        }
        if (Short.class == clazz || short.class == clazz) {
            return Short.valueOf(value.equals("")?"0":value);
        }
        if (Byte.class == clazz || byte.class == clazz) {
            return Byte.valueOf(value.equals("")?"0":value);
        }
        if (Character.class == clazz || char.class == clazz) {
            return (value.equals("")?"0":value).charAt(0);
        }
        if (Long.class == clazz || long.class == clazz) {
            return Long.valueOf(value.equals("")?"0":value);
        }
        if (Float.class == clazz || float.class == clazz) {
            return Float.valueOf(value.equals("")?"0":value);
        }
        if (Double.class == clazz || double.class == clazz) {
            return Double.valueOf(value.equals("")?"0":value);
        }
        if (Boolean.class == clazz || boolean.class == clazz) {
            return Boolean.valueOf(value.equals("")?"0":value.toLowerCase());
        }
        if (BigDecimal.class == clazz) {
            return new BigDecimal(value.equals("")?"0":value);
        }
        if(LocalDateTime.class==clazz){
            return LocalDateTime.parse(value);
        }
        if (LocalDate.class==clazz){
            return LocalDate.parse(value);
        }
        return value;
    }
    @SneakyThrows
    public String getExtension(InputStream inputStream)
    {
        String result="";
        FileMagic fileMagic=FileMagic.valueOf(inputStream);
        if (Objects.equals(fileMagic,FileMagic.OLE2)){
            result=".xls";
        }
        if(Objects.equals(fileMagic,FileMagic.OOXML)){
            result=".xlsx";
        }
        return result;
    }

}